﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DBBase;
using System.IO;

using System.Reflection; // 引用这个才能使用Missing字段
using System.Xml;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;

namespace Business
{
    public partial class Danger : Form
    {


        ArrayList ccllx = new ArrayList();
        public int ijilu;
        public Danger()
        {
            InitializeComponent();
           
            ijilu = 0;
            getcllxnew();
        }
      
        public void getcllx()
        {
            Query mquery = new Query();
            string sql;
            sql = string.Format(@"Select distinct EXA_INSPECT from Examines ");
            DataTable dt = mquery.GetTable(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ijilu = i;
                    if (ijilu > 20)
                    {
                        ijilu = 20;
                    }
                    ccllx[ijilu] = dt.Rows[i]["EXA_INSPECT"].ToString().Trim();
                }
            }
            ccllx[0] = "客";
            ccllx[1] = "卧铺";
            ccllx[2] = "危化品运输";
            ccllx[3] = "易燃爆";
            ccllx[4] = "校";
            ijilu = 5;
        }

        public void getcllxnew()
        {
            Query mquery = new Query();
            string sql;
            sql = string.Format(@"Select distinct DET_JCLB from Detects_SJA ");
            DataTable dt = mquery.GetTable(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i]["DET_JCLB"].ToString().Trim().Length > 1)
                    {
                        ccllx.Add(dt.Rows[i]["DET_JCLB"].ToString().Trim());
                        ijilu++;
                    }
                }
            }
            ccllx[0] = "客";
            ccllx[1] = "卧铺";
            ccllx[2] = "危化品运输";
            ccllx[3] = "易燃爆";
            ccllx[4] = "校";
            ijilu = 5;
        }
        public void getneirong()
        {
            string sql = "";
            string sql2 = "";
            string sql3 = "";
            string sql4 = "";
            Query mquery = new Query();
            for (int i = 0; i < ijilu; i++)
            {
                sql = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_CLLX like '%%{0}%%'", ccllx[i]);

                int izong = mquery.selectDBcount(sql);

                sql2 = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_CLLX like '%%{0}%%' and DET_JYZJL=1", ccllx[i]);

                int izong2 = mquery.selectDBcount(sql2);

                sql3 = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_CLLX like '%%{0}%%' and DET_JYZJL=1 and DET_JYCS=1", ccllx[i]);

                int izong3 = mquery.selectDBcount(sql3);

                sql4 = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_CLLX like '%%{0}%%' and DET_JYZJL=1 and DET_JYZJL=1 and DET_JYCS>1", ccllx[i]);

                int izong4 = mquery.selectDBcount(sql4);
            }
        }
        
      
        void inicbx(ComboBox cbx, string dataname)
        {
            Query objquery = new Query();
            string ssqlex = "";
            ssqlex = string.Format(@"select * from {0}", dataname);
            DataTable dt = objquery.GetTable(ssqlex);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cbx.Items.Add(dt.Rows[i]["table_2"].ToString().Trim());
                if (i == 0)
                    cbx.Text = dt.Rows[0]["table_2"].ToString().Trim();
            }
        }
       
        private void excel(string path)
        {
            try
            {
                StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("gb2312"));
                StringBuilder sb = new StringBuilder();
                // sb.Append("地区名" + "\t");
                sb.Append("统计时间从" + date_kaishi.Value.ToShortDateString() + "至" + date_jieshu.Value.ToShortDateString() + "\t");
                sb.Append(Environment.NewLine);
                for (int i = 0; i < lv.Columns.Count; i++)
                    sb.Append(lv.Columns[i].Text.ToString() + "\t");
                sb.Append(Environment.NewLine);

                foreach (ListViewItem li in lv.Items)
                {
                    for (int i = 0; i < lv.Columns.Count; i++)
                        sb.Append(li.SubItems[i].Text + "\t");
                    sb.Append(Environment.NewLine);
                }
                sw.Write(sb.ToString());
                sw.Flush();
                sw.Close();
                MessageBox.Show("成功保存于" + path + "文件！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void weixiankechetongji_Load(object sender, EventArgs e)
        {
           // inicbx(comboBox1,"table_cllx");
            //判断是否民警
            //验证使用
            //if (m_mainform.sfjy.Contains("检测数据统计"))
            //{
            //    if (!m_mainform.SFMJ.Contains("是"))
            //    {
            //        skinButton1.Enabled = false;
            //    }
            //}
        }
        public MainForm m_mainform;
        private void skinButton1_Click(object sender, EventArgs e)
        {
            //判断是否核心功能
            //if (m_mainform.shxgn.Contains("检测数据统计"))
            //{
            //    m_mainform.baocunhxrj("检测数据统计", string.Format("检测数据统计"), "查询");// 核心功能日记  日记内容
            //}
            Query mquery = new Query();
            DataTable dtable = new DataTable("Rock");
            //set columns names
            dtable.Columns.Add("1", typeof(System.String));
            dtable.Columns.Add("2", typeof(System.String));
            dtable.Columns.Add("3", typeof(System.String));
            dtable.Columns.Add("4", typeof(System.String));
            dtable.Columns.Add("5", typeof(System.String));


            string sql = "";

            string sql3 = "";
            string sql4 = "";
            int izs1 = 0;
            int izs3 = 0;
            int izs4 = 0;
            DataRow drow = dtable.NewRow();
            for (int i = 0; i < ijilu; i++)
            {
                sql = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_CLLX like '%%{0}%%' and Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}'", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong = mquery.selectDBcount(sql);
                if (i == 2 || i == 3 || i == 4)
                {
                    sql = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_SYXZ like '%%{0}%%' and Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}'", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");
                    izong = mquery.selectDBcount(sql);
                }


                izs1 = izs1 + izong;

                //sql2 = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}' and DET_OK=1", ccllx[i]);

                //int izong2 = selectDBcount(sql2);

                sql3 = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_CLLX like '%%{0}%%' and Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}' and DET_JYZJL=1 and DET_JYCS=1", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong3 = mquery.selectDBcount(sql3);
                if (i == 2 || i == 3 || i == 4)
                {
                    sql3 = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_CLLX like '%%{0}%%' and Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}'and DET_JYZJL=1 and DET_JYCS=1", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");
                    izong3 = mquery.selectDBcount(sql);
                }
                izs3 = izs3 + izong3;

                sql4 = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_CLLX like '%%{0}%%' and Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}' and DET_JYCS>1", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong4 = mquery.selectDBcount(sql4);
                if (i == 2 || i == 3 || i == 4)
                {
                    sql4 = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA Where AUTO.AT_ID=Detects_SJA.AT_ID and AT_SYXZ like '%%{0}%%' and Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}' and DET_JYCS>1", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                    izong4 = mquery.selectDBcount(sql4);
                }
                izs4 = izs4 + izong4;

                drow = dtable.NewRow();
                string sneirong = "";
                if (i == 0)
                {
                    sneirong = "客车(座椅)";
                }
                else if (i == 1)
                {
                    sneirong = "客车(卧铺)";
                }
                else if (i == 2)
                {
                    sneirong = "危货(易燃易爆)";
                }
                else if (i == 3)
                {
                    sneirong = "危贷(非易燃易爆)";
                }
                else if (i == 4)
                {
                    sneirong = "校车";
                }

                drow["1"] = string.Format("{0}", sneirong);
                drow["2"] = string.Format("{0}", izong);
                drow["3"] = string.Format("{0}", izong3);
                drow["4"] = string.Format("{0}", i == 0 ? "号牌号码/车辆类型" : "");
                drow["5"] = string.Format("{0}%", 100 * izong4 / (izong > 0 ? izong : 1));

                dtable.Rows.Add(drow);
            }
            drow = dtable.NewRow();


            drow["1"] = string.Format("{0}", "合计");
            drow["2"] = string.Format("{0}", izs1);
            drow["3"] = string.Format("{0}", izs3);
            drow["4"] = string.Format("{0}", "");
            drow["5"] = string.Format("{0}%", 100 * izs4 / (izs1 > 0 ? izs1 : 1));

            dtable.Rows.Add(drow);


            multiColHeaderDgv2.DataSource = dtable;
        }

        private void but_daochu_Click(object sender, EventArgs e)
        {
            string path = "";
            path = Application.StartupPath + path + DateTime.Now.ToString("yyyyMMdd") + ".xls";
            excel(path);
        }
    }
}
